package br.ucb.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import br.ucb.connection.Dados;
import br.ucb.connection.FactoryConnection;
import br.ucb.entity.Disciplina;

public class DisciplinaDAO {

	/* Insere Disciplina */
	public static void insertDisciplina(Disciplina disciplina)
			throws SQLException, ClassNotFoundException {
		Connection con = FactoryConnection.getInstance().criaConexao();
		PreparedStatement ps = null;
		String SQL_INSERT = "INSERT INTO Disciplina(nomeDisciplina,objetivo,cargaHoraria,nivelMedio)"
				+ "values(?,?,?,?);";

		ps = con.prepareStatement(SQL_INSERT);
		ps.setString(1, disciplina.getNomeDisciplina());
		ps.setString(2, disciplina.getObjetivo());
		ps.setInt(3, disciplina.getCargaHoraria());
		ps.setDouble(4, disciplina.getNivelMedio());

		ps.executeUpdate();

		con.close();
		ps.close();
	}
	/*Deleta Disciplina*/
	public static void deletaDisciplina (Integer idDisciplina)throws SQLException, ClassNotFoundException{
		Connection con = FactoryConnection.getInstance().criaConexao();
		PreparedStatement ps = null;
		String SQL_DELETE =  "DELETE FROM Disciplina WHERE idDisciplina = ?;";
		
		ps = con.prepareStatement(SQL_DELETE); 
		ps.setInt(1,idDisciplina);
		ps.executeUpdate();
		
		con.close();
		ps.close();
	}
	/* Insere Disciplina */
	public static void insertDisciplinaSae(Disciplina disciplina ,Dados dados)
			throws SQLException, ClassNotFoundException {
		Connection con = FactoryConnection.getInstance().criaConexao(dados);
		PreparedStatement ps = null;
		String SQL_INSERT = "INSERT INTO maiordisciplina(nomeDisciplina,objetivo,cargaHoraria,nivelMedio)"
				+ "values(?,?,?,?);";

		ps = con.prepareStatement(SQL_INSERT);
		ps.setString(1, disciplina.getNomeDisciplina());
		ps.setString(2, disciplina.getObjetivo());
		ps.setInt(3, disciplina.getCargaHoraria());
		ps.setDouble(4, disciplina.getNivelMedio());

		ps.executeUpdate();

		con.close();
		ps.close();
	}
	/* Procura id da ultima disciplina inserida no banco*/
	public static Integer getLastIdSae(Dados dados) throws SQLException, ClassNotFoundException {
		Integer id = 0;
		Connection con = FactoryConnection.getInstance().criaConexao(dados);
		String SQL_SELECT = "SELECT last_insert_id(idDisciplina) from maiordisciplina;";
		PreparedStatement ps = null;
		ResultSet rs = null;
		ps = con.prepareStatement(SQL_SELECT);
		// ps.setInt(1,id_bolao);
		rs = ps.executeQuery();
		while (rs.next()) {
			id = rs.getInt(1);
		}
		ps.close();
		con.close();
		return id;
	}
	
	
	public static void updateDisciplina(Disciplina disciplina)
			throws SQLException, ClassNotFoundException {
		Connection con = FactoryConnection.getInstance().criaConexao();
		PreparedStatement ps = null;
		String SQL_INSERT = "UPDATE Disciplina set nomeDisciplina = ?, objetivo = ? , cargaHoraria = ?, nivelMedio = ? where idDisciplina = ?;";

		ps = con.prepareStatement(SQL_INSERT);
		ps.setString(1, disciplina.getNomeDisciplina());
		ps.setString(2, disciplina.getObjetivo());
		ps.setInt(3, disciplina.getCargaHoraria());
		ps.setDouble(4, disciplina.getNivelMedio());
		ps.setInt(5, disciplina.getIdDisciplina());
		ps.executeUpdate();

		con.close();
		ps.close();
	}
	
	/* Procura id da ultima disciplina inserida no banco*/
	public static Integer getLastId() throws SQLException, ClassNotFoundException {
		Integer id = 0;
		Connection con = FactoryConnection.getInstance().criaConexao();
		String SQL_SELECT = "SELECT last_insert_id(idDisciplina) from Disciplina;";
		PreparedStatement ps = null;
		ResultSet rs = null;
		ps = con.prepareStatement(SQL_SELECT);
		// ps.setInt(1,id_bolao);
		rs = ps.executeQuery();
		while (rs.next()) {
			id = rs.getInt(1);
		}
		ps.close();
		con.close();
		return id;
	}
	/* Procura Disciplina pegando o ultimo id que foi inserido no banco */
	public static Disciplina getDisciplinaByName(String nomeDisciplina) throws SQLException,ClassNotFoundException {
		Disciplina disciplina = null;
		Connection con = FactoryConnection.getInstance().criaConexao();
		String SQL_SELECT = "SELECT * FROM Disciplina WHERE nomeDisciplina = ?;";
		PreparedStatement ps = null;
		ResultSet rs = null;
		ps = con.prepareStatement(SQL_SELECT);
		ps.setString(1,nomeDisciplina);
		rs = ps.executeQuery();
		while (rs.next()) {
			disciplina = new Disciplina(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getInt(4), rs.getInt(5));
		}
		ps.close();
		con.close();
		return disciplina;
	}
	/* Procura Disciplina pegando o ultimo id que foi inserido no banco */
	public static Disciplina getDisciplinaByLastId() throws SQLException,ClassNotFoundException {
		Disciplina disciplina = null;
		Integer idDisciplina = DisciplinaDAO.getLastId();
		Connection con = FactoryConnection.getInstance().criaConexao();
		String SQL_SELECT = "SELECT * FROM Disciplina WHERE idDisciplina = ?;;";
		PreparedStatement ps = null;
		ResultSet rs = null;
		ps = con.prepareStatement(SQL_SELECT);
		ps.setInt(1,idDisciplina);
		rs = ps.executeQuery();
		while (rs.next()) {
			disciplina = new Disciplina(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getInt(4), rs.getInt(5));
		}
		ps.close();
		con.close();
		return disciplina;
	}
	
	/* Busca as disciplinas do Banco*/
		public static List<Disciplina> getDisciplinas() throws SQLException, ClassNotFoundException{
			List<Disciplina> disciplinas = new ArrayList<Disciplina>();
			Disciplina disciplina = DisciplinaDAO.getDisciplinaByLastId();
			String SQL_SELECT = "SELECT * FROM Disciplina";
			PreparedStatement ps = null;
			ResultSet rs = null;
			Connection con = FactoryConnection.getInstance().criaConexao();
			ps = con.prepareStatement(SQL_SELECT);
			rs = ps.executeQuery();
			while(rs.next()){
				disciplinas.add(new Disciplina(rs.getInt(1),rs.getString(2),rs.getString(3),rs.getInt(4),rs.getInt(5)));
			}
			con.close();
			ps.close();
			if(disciplinas.size() == 0)
				return null;
			return disciplinas;
			
		}
	
}
